﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Script.Services;
using DAO;
using System.Data.SqlClient;
using System.Text;
using System.Xml;
using System.Web.Script.Serialization;

/// <summary>
/// Summary description for UniversalType
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ScriptService]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class UniversalType : System.Web.Services.WebService
{

    public UniversalType()
    {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    [WebMethod(Description = "")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Xml)]
    public XmlDocument LoadAll(int page, int pageSize, string sidx, string sord, string letter, int type, int lang)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            string q = "";
            if (letter == "") q = "SELECT count(id) as countid FROM dbo.Fn_GetAllUniversalTypes(0, 1000000, 'id', 'asc', " + type + ", " + lang + ")";
            else q = "SELECT count(id) as countid FROM dbo.Fn_GetAllUniversalTypes(0, 1000000, 'id', 'asc', " + type + ", " + lang + ")"
                + " WHERE UPPER(SUBSTRING(name, 1, 1)) = '" + letter.ToUpper() + "'";
            using (SqlCommand cmd = new SqlCommand(q, conn))
            {
                SqlDataReader dr = null;
                int start = page * pageSize - pageSize;
                if (start < 0) start = 0;
                int limit = pageSize * page;

                StringBuilder str = new StringBuilder();
                SqlParameter[] pars = new SqlParameter[6];
                pars[0] = new SqlParameter("start", start);
                pars[1] = new SqlParameter("limit", limit);
                pars[2] = new SqlParameter("order", sidx);
                pars[3] = new SqlParameter("orderdir", sord);
                pars[4] = new SqlParameter("type_id", type);
                pars[5] = new SqlParameter("language_id", lang);

                int total = 0;
                dr = cmd.ExecuteReader();
                if (dr != null)
                {
                    if (dr.HasRows)
                    {
                        if (dr.Read()) Int32.TryParse(dr["countid"].ToString(), out total);
                    }
                }
                dr.Close();
                if (letter == "") cmd.CommandText = "SELECT * FROM dbo.Fn_GetAllUniversalTypes(@start, @limit, @order, @orderdir, @type_id, @language_id) ORDER By " + sidx + " " + sord;
                else cmd.CommandText = "SELECT * FROM dbo.Fn_GetAllUniversalTypes(@start, @limit, @order, @orderdir, @type_id, @language_id)"
                    + " WHERE UPPER(SUBSTRING(name, 1, 1)) = '" + letter.ToUpper() + "' ORDER By " + sidx + " " + sord;
                foreach (SqlParameter s in pars)
                    cmd.Parameters.Add(s);
                SqlDataReader dr1 = cmd.ExecuteReader();
                XmlDocument d = new XmlDocument();
                if (dr1 != null)
                {
                    if (dr1.HasRows)
                    {
                        XmlDeclaration dec =
                            d.CreateXmlDeclaration("1.0", "utf-8", null);
                        d.AppendChild(dec);
                        XmlNode root = d.CreateElement("rows");
                        d.AppendChild(root);
                        XmlNode _page = d.CreateElement("page");
                        _page.InnerText = page.ToString();
                        root.AppendChild(_page);
                        XmlNode _total = d.CreateElement("total");
                        _total.InnerText = Math.Ceiling((double)total / pageSize).ToString();
                        root.AppendChild(_total);
                        XmlNode _records = d.CreateElement("records");
                        _records.InnerText = total.ToString();
                        root.AppendChild(_records);
                        XmlElement el;
                        while (dr1.Read())
                        {
                            el = d.CreateElement("row");
                            el.SetAttribute("id", dr1["id"].ToString());
                            XmlElement cell = d.CreateElement("cell");
                            cell.InnerText = dr1["id"].ToString();
                            el.AppendChild(cell);
                            cell = d.CreateElement("cell");
                            XmlCDataSection cdata = d.CreateCDataSection(dr1["name"].ToString());
                            cell.AppendChild(cdata);
                            el.AppendChild(cell);
                            cell = d.CreateElement("cell");
                            cell.InnerText = dr1["isActive"].ToString();
                            el.AppendChild(cell);
                            root.AppendChild(el);
                        }
                    }
                }
                if (dr1 != null && !dr1.IsClosed) dr1.Close();
                return d;
            }
        }
    }


    public class UnivTypeObj
    {
        public long _id { get; set; }
        public int _language_id { get; set; }
        public long _parent_id { get; set; }
        public long _type_id { get; set; }
        public string _name { get; set; }
        public string _parent_name { get; set; }
        public bool _isActive { get; set; }
    }

    [WebMethod(Description = "")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string getUnivType(long id)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT * from dbo.Fn_GetUniversalType(@id)", conn))
            {
                StringBuilder str = new StringBuilder();
                SqlParameter[] pars = new SqlParameter[1];
                pars[0] = new SqlParameter("id", id);

                foreach (SqlParameter s in pars)
                    cmd.Parameters.Add(s);
                SqlDataReader dr1 = cmd.ExecuteReader();
                UnivTypeObj l = new UnivTypeObj();
                if (dr1.Read())
                {
                    long _id = 0;
                    int language_id;
                    long _parent_id;
                    long _type_id;
                    string _name = "";
                    string parent_name = "";
                    bool isActive = false;
                    Int64.TryParse(dr1["id"].ToString(), out _id);
                    Int32.TryParse(dr1["language_id"].ToString(), out language_id);
                    Int64.TryParse(dr1["parent_id"].ToString(), out _parent_id);
                    Int64.TryParse(dr1["type_id"].ToString(), out _type_id);
                    _name = dr1["name"].ToString();
                    parent_name = dr1["parent_name"].ToString();
                    bool.TryParse(dr1["isActive"].ToString(), out isActive);
                    l._id = _id;
                    l._name = _name;
                    l._language_id = language_id;
                    l._parent_id = _parent_id;
                    l._type_id = _type_id;
                    l._isActive = isActive;
                }

                if (dr1 != null && !dr1.IsClosed) dr1.Close();

                List<UnivTypeObj> lst = new List<UnivTypeObj>();
                lst.Add(l);
                JavaScriptSerializer js = new JavaScriptSerializer();
                string strJSON = js.Serialize(l);
                return strJSON;
            }
        }
    }

    [WebMethod(Description = "")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string getUniverTypeByType(Int64 typeid, int language_id)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT * from dbo.Fn_GetTypeByType(@language_id,@type_id,0,130,'id','desc') WHERE isActive='true'", conn))
            {
                SqlParameter[] pars = new SqlParameter[2];
                pars[0] = new SqlParameter("language_id", language_id);
                pars[1] = new SqlParameter("type_id", typeid);

                foreach (SqlParameter s in pars)
                    cmd.Parameters.Add(s);
                SqlDataReader dr_2 = cmd.ExecuteReader();

                Dictionary<string, string> msg = new Dictionary<string, string>();

                while (dr_2.Read())
                {
                    bool isActive = false;
                    bool.TryParse(dr_2["isActive"].ToString(), out isActive);
                    if (isActive)
                    {
                        msg.Add(dr_2["id"].ToString(), dr_2["name"].ToString());
                    }
                }
                dr_2.Close();
                JavaScriptSerializer js = new JavaScriptSerializer();
                string strJSON = js.Serialize(msg);
                return strJSON;
            }
        }
    }


    [WebMethod(Description = "")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string getUnivTypeByLang(long id, int language)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            string q = "";
            if (language == DAO.Connection.DEFAULT_LANGUAGE)
            {
                q = "SELECT * from dbo.Fn_GetUniversalTypeById(@id, @language_id)";
            }
            else
            {
                q = "SELECT * from dbo.Fn_GetUniversalTypeByLang(@id, @language_id)";
            }
            using (SqlCommand cmd = new SqlCommand(q, conn))
            {
                StringBuilder str = new StringBuilder();
                SqlParameter[] pars = new SqlParameter[2];
                pars[0] = new SqlParameter("id", id);
                pars[1] = new SqlParameter("language_id", language);

                foreach (SqlParameter s in pars)
                    cmd.Parameters.Add(s);
                SqlDataReader dr1 = cmd.ExecuteReader();
                UnivTypeObj l = new UnivTypeObj();
                if (dr1.Read())
                {
                    long _id = 0;
                    int language_id;
                    long _parent_id;
                    long _type_id;
                    string _name = "";
                    string parent_name = "";
                    bool isActive = false;
                    Int64.TryParse(dr1["id"].ToString(), out _id);
                    Int32.TryParse(dr1["language_id"].ToString(), out language_id);
                    Int64.TryParse(dr1["parent_id"].ToString(), out _parent_id);
                    Int64.TryParse(dr1["type_id"].ToString(), out _type_id);
                    _name = dr1["name"].ToString();
                    parent_name = dr1["parent_name"].ToString();
                    bool.TryParse(dr1["isActive"].ToString(), out isActive);
                    l._id = _id;
                    l._name = _name;
                    l._language_id = language_id;
                    l._parent_id = _parent_id;
                    l._type_id = _type_id;
                    l._isActive = isActive;
                }

                if (dr1 != null && !dr1.IsClosed) dr1.Close();

                List<UnivTypeObj> lst = new List<UnivTypeObj>();
                lst.Add(l);
                JavaScriptSerializer js = new JavaScriptSerializer();
                string strJSON = js.Serialize(l);
                return strJSON;
            }
        }

    }

    [WebMethod(Description = "")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string updateUniveType(long id, long orig_id, string name, int language_id, int type_id, bool isActive)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            string q = "";
            if (id == orig_id)
            {
                //update the original copy
                q = "SP_UpdateUnivType";
            }
            else
            {
                if (id == 0)
                {
                    //insert new item
                    q = "SP_InsUnivType";
                }
                else
                {
                    //update existing with different language
                    //pars[0] = new SqlParameter("id", orig_id);
                    q = "SP_UpdateUnivType";

                }
                //if not exist insert the record in the database
            }
            using (SqlCommand cmd = new SqlCommand(q, conn))
            {
                SqlParameter[] pars = new SqlParameter[6];
                pars[0] = new SqlParameter("id", id);
                pars[1] = new SqlParameter("orig_id", orig_id);
                pars[2] = new SqlParameter("name", name);
                pars[3] = new SqlParameter("language_id", language_id);
                pars[4] = new SqlParameter("type_id", type_id);
                pars[5] = new SqlParameter("isActive", isActive);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (SqlParameter s in pars)
                    cmd.Parameters.Add(s);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                JavaScriptSerializer js = new JavaScriptSerializer();

                return (val > 0) ? js.Serialize("true") : js.Serialize("false");
            }
        }
    }

    [WebMethod(Description = "")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string insUnivType(string name, int language_id, int type_id, bool isActive)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SP_InsUnivTypeRoot", conn))
            {
                SqlParameter[] pars = new SqlParameter[4];
                pars[0] = new SqlParameter("name", name);
                pars[1] = new SqlParameter("language_id", Connection.DEFAULT_LANGUAGE);
                pars[2] = new SqlParameter("type_id", type_id);
                pars[3] = new SqlParameter("isActive", isActive);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (SqlParameter s in pars)
                    cmd.Parameters.Add(s);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                JavaScriptSerializer js = new JavaScriptSerializer();

                return (val > 0) ? js.Serialize("true") : js.Serialize("false");
            }
        }
    }

}

